<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;
class OracleDatabase extends SQLDatabase {
    private $dbh;

    protected static $sql_var = array(
        'current_date_utc'       => "trunc(sysdate at time zone 'UTC')",
        'current_date_local'     => "trunc(sysdate)",
        'current_datetime_utc'   => "(sysdate at time zone 'UTC')",
        'current_datetime_local' => "sysdate"
    );

    public function __construct($context, $type, $name, $conf) {
        global $trace;
        if ($trace) trace_entry();
        parent::__construct($context, $type, $name, $conf);
        $this->case_sensitive = 1;
        $this->func_strtoupper = 'upper';
        $this->func_strtolower = 'lower';
        if ($trace) trace_exit();
    }

    protected function _connect () {
        global $trace;
        if ($trace) trace_entry();
        #echo "_connect() : $this->dbuser/$this->dbpass@$this->dbhost\n";
        $this->dbh = oci_connect($this->dbuser, $this->dbpass, $this->_dsn());
        #echo "_connect() : connected [$this->dbh]\n";
        if (!$this->dbh)
            throw new \Exception('Could not connect to database.');
        if ($trace) trace_exit();
    }

    protected function _disconnect () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dbh)) {
            oci_close($this->dbh);
        }
        if ($trace) trace_exit();
    }

    ##################################################################################
    # From: http://php.net/manual/en/function.oci-connect.php
    ##################################################################################
    # The "connection string" or "dsn" contains the Oracle instance to connect to.
    # It can be an » Easy Connect string, or a Connect Name from the tnsnames.ora file, or the name of a local Oracle instance.
    # If not specified, PHP uses environment variables such as TWO_TASK (on Linux) or LOCAL (on Windows)
    # and ORACLE_SID to determine the Oracle instance to connect to.
    # To use the Easy Connect naming method, PHP must be linked with Oracle 10g or greater Client libraries.
    # The Easy Connect string for Oracle 10g is of the form: [//]host_name[:port][/service_name].
    # With Oracle 11g, the syntax is: [//]host_name[:port][/service_name][:server_type][/instance_name].
    # Service names can be found by running the Oracle utility lsnrctl status on the database server machine.
    # The tnsnames.ora file can be in the Oracle Net search path, which includes $ORACLE_HOME/network/admin and /etc.
    # Alternatively set TNS_ADMIN so that $TNS_ADMIN/tnsnames.ora is read. Make sure the web daemon has read access to the file.

    protected function _dsn () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dsn)) {
            $dsn = $this->dsn;
        }
        elseif (isset($this->schema_name)) {
            $dsn = $this->schema_name;
        }
        if (!isset($dsn)) {
            throw new \Exception("Could not determine the appropriate DSN for the requested database ($this->name).");
        }
        if ($trace) trace_exit($dsn);
        return($dsn);
    }

    # NOTE: Oracle's schemas are associated with users.
    # If users share the same tables, it is because there are synonyms created that make them visible to other schemas.
    protected function _assumed_schema_name() {
        global $trace;
        if ($trace) trace_entry();
        if ($trace) trace_exit($this->dbuser);
        return $this->dbuser;
    }

    # Oracle users are usually only to see their own schema.
    # Privileged users can see multiple schemas.
    #    select sch.grantee as schema_name from dba_role_privs sch where sch.granted_role = 'SELECT_CATALOG_ROLE';
    protected function _get_schema_defs_from_db () {
        global $trace;
        if ($trace) trace_entry();
        $schema_defs = Array($this->schema_name => Array('schema_name' => $this->schema_name, 'physical_ind' => 'Y'));
        $schemas     = Array($this->schema_name);
        try {
#            $sql = <<<EOF
#select schema_name from (
#   select sch.grantee as schema_name from dba_role_privs sch where sch.granted_role = 'SELECT_CATALOG_ROLE'
#   union
#   select distinct owner as schema_name from all_tables t
#)
#where exists (select 1 from all_tables tab where tab.owner = schema_name)
#order by schema_name
#EOF;
            $sql = "select distinct owner as schema_name from all_tables t";
            $more_schemas = $this->get_column($sql);
            foreach ($more_schemas as $new_schema) {
                $lc_schema = strtolower($new_schema);
                if ($lc_schema != $this->schema_name) {
                    $schemas[] = $lc_schema;
                    $schema_defs[$lc_schema] = Array('schema_name' => $lc_schema, 'physical_ind' => 'Y');
                }
            }
        }
        catch (Exception $e) {
            # do nothing. oh well. It seems like you are not privileged enough to see if you have more schemas than your own.
        }
        if ($trace) trace_exit($schema_defs);
        return($schema_defs);
    }

    protected function _get_table_defs_from_db ($schema) {
        global $trace;
        if ($trace) trace_entry();
        if ($schema == $this->schema_name) {
            $sql = "select tab.table_name from user_tables tab where tab.table_name not like '%$%' order by tab.table_name";
        }
        else {
            $uc_schema = strtoupper($schema);
            $sql = "select tab.table_name from all_tables tab where tab.owner = '$uc_schema' and tab.table_name not like '%$%' order by tab.table_name";
        }
        $rows = $this->get_rows($sql);
        $table_defs = Array();
        foreach ($rows as $row) {
            $table_name = strtolower($row[0]);
            $table_defs[$table_name] = Array('table_name' => $table_name, 'physical_ind' => 'Y');
        }
        if ($trace) trace_exit($table_defs);
        return($table_defs);
    }

    #CREATE TABLE db_column (
    # *column_name              VARCHAR(99)      NOT NULL,
    # *physical_ind             VARCHAR(1)       NULL,
    # *column_type              VARCHAR(99)      NOT NULL,
    # *order_idx                INTEGER          NOT NULL DEFAULT 9999,
    # *max_length               INTEGER          NULL,
    # *not_null_ind             VARCHAR(1)       NULL,
    # *default_value            VARCHAR(99)      NULL,

    protected function _get_column_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema)) $schema = $this->schema_name;
        $uc_schema = strtoupper($schema);
        $uc_table  = strtoupper($table);
        if ($schema == $this->schema_name) {
            $sql = "select c.column_id, c.column_name, c.data_type, c.data_length, c.data_precision, c.data_scale, c.nullable, c.data_default, c.avg_col_len, c.char_length from user_tab_columns c where c.table_name = '$uc_table' order by c.column_id";
        }
        else {
            $sql = "select c.column_name, c.column_id, c.data_type, c.data_length, c.data_precision, c.data_scale, c.nullable, c.data_default, c.avg_col_len, c.char_length from all_tab_columns c where c.owner = '$uc_schema' and c.table_name = '$uc_table' order by c.column_id";
        }
        $rows = $this->get_hashes($sql);
        $column_defs = Array();
        foreach ($rows as $row) {
            $column_name                 = strtolower($row['column_name']);
            $column_defs[$column_name]   = Array('column_name' => $column_name, 'physical_ind' => 'Y');
            $column_def                  =& $column_defs[$column_name];
            $column_def['order_idx']     = $row['column_id'];
            $column_type                 = $this->native_type_to_app_type($row['data_type'], $row['data_length'], $row['data_scale'], $column_name);
            $column_def['column_type']   = $column_type;
            $column_def['max_length']    = ($column_type == 'string' || $column_type == 'text' || $column_type == 'blob') ? $row['data_length'] : $row['data_precision'];
            $column_def['not_null_ind']  = ($row['nullable'] == 'Y') ? 'N' : 'Y';
            $column_def['default_value'] = $row['data_default'];
        }
        if ($trace) trace_exit($column_defs);
        return($column_defs);
    }

    protected function _get_index_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        #$sql = "select (case i.constraint_name when 'PRIMARY' then concat(i.table_name,'.PRIMARY') else i.constraint_name end) as index_name from information_schema.table_constraints i where i.table_schema = '$schema' and i.table_name = '$table' and constraint_type in ('PRIMARY KEY','UNIQUE') order by constraint_type, constraint_name";
        #$index_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'index_name');
        $index_defs = Array();
        if ($trace) trace_exit($index_defs);
        return($index_defs);
    }

    protected function _get_index_columns_from_db ($schema, $table, $index_defs, $column_defs) {
        global $trace;
        if ($trace) trace_entry();
        $all_index_rows = Array();
        #foreach ($index_defs as $index_name => $index_def) {
        #    $index_rows = $this->get_rows("pragma table_info('$index_name')");
        #    foreach ($index_rows as $index_row) {
        #        $row = Array();
        #        $row['order_idx']       = $index_row[0]+1;
        #        $row['index_id']        = $index_def['index_id'];
        #        $row['column_id']       = $column_defs[$index_row[2]]['column_id'];
        #        $all_index_rows[]       = $row;
        #    }
        #}
        if ($trace) trace_exit($all_index_rows);
        return($all_index_rows);
    }

    protected function _get_relationships_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        #$rows = $this->get_rows("pragma foreign_key('$table')");    # cid|name|type|notnull|dflt_value|pk
        $relationships = Array();
        #foreach ($rows as $row) {
        #    $relationship = Array();
        #    $relationship['order_idx']       = $row[0]+1;
        #    $relationship['column_name']     = strtolower($row[1]);
        #    $relationship['column_type']     = $this->native_type_to_app_type(strtolower($row[2]));
        #    $relationship['max_length']      = $row[3] ? $row[3] : null;
        #    $relationship['default_value']   = $row[4] ? $row[4] : null;
        #    $relationship['primary_key_ind'] = $row[5] ? 'Y' : 'N';
        #    $relationship['physical_ind'] = 'Y';
        #    $relationships[$relationship['column_name']] = $relationship;
        #}
        if ($trace) trace_exit($relationships);
        return($relationships);
    }

#    protected function _get_native_column_defs ($table, $schema_name) {
#        global $trace;
#        if ($trace) trace_entry();
#        $uc_table = strtoupper($table);
#        if ($schema_name == $this->schema_name) {
#            $sql = "select col.column_name, col.data_type, col.data_length, col.data_precision, col.data_scale, col.nullable, col.data_default, col.avg_col_len, col.char_length from user_tab_columns col where col.table_name = '$uc_table' order by col.column_id";
#        }
#        else {
#            $uc_schema = strtoupper($schema_name);
#            $sql = "select col.column_name, col.data_type, col.data_length, col.data_precision, col.data_scale, col.nullable, col.data_default, col.avg_col_len, col.char_length from dba_tab_columns col where col.owner = '$uc_schema' and col.table_name = '$uc_table' order by col.column_id";
#        }
#        $column_defs = $this->get_hashes($sql);
#        if ($trace) trace_exit($column_defs);
#        return($column_defs);
#    }
#
#    protected function _get_column_defs ($table, $schema_name, &$table_def) {
#        global $trace;
#        if ($trace) trace_entry();
#        $fulltable = "$schema_name.$table";
#
#        if (! isset($table_def['columns'])) $table_def['columns'] = array();
#        $columns =& $table_def['columns'];
#           
#        if (!isset($table_def['column'])) {
#            $table_def['column'] = Array();
#        }
#        $column_defs =& $table_def['column'];
#
#        $native_column_defs = $this->_get_native_column_defs($table, $schema_name);
#        # $table_def['native_column_defs'] = $native_column_defs;    # (I'm not sure we really need to keep this around)
#
#        $table_def['primary_key'] = Array();
#        $primary_key =& $table_def['primary_key'];
#
#        $column_alias_used = Array();
#        for ($i = 0; $i < count($native_column_defs); $i++) {
#            $native_column_def = $native_column_defs[$i];
#
#            $column         = strtolower($native_column_def['column_name']);
#            $data_type      = strtolower($native_column_def['data_type']);
#            $data_length    = $native_column_def['data_length'];
#            $data_precision = $native_column_def['data_precision'];
#            $data_scale     = $native_column_def['data_scale'];
#            $nullable       = strtolower($native_column_def['nullable']);
#            $data_default   = $native_column_def['data_default'];
#            $avg_col_len    = $native_column_def['avg_col_len'];
#            $char_length    = $native_column_def['char_length'];
#
#            if (! isset($column_defs[$column])) $column_defs[$column] = array();
#            $column_def =& $column_defs[$column];
#           
#            if (! isset($column_def['alias'])) {
#                $column_def['alias'] = $this->make_alias($column, $column_alias_used);
#            }
#            else {
#                $alias = $column_def['alias'];
#                if (isset($column_alias_used[$alias])) {
#                    $column_def['alias'] = $this->make_alias($column, $column_alias_used);
#                }
#            }
#            if (! isset($column_def['label'])) $column_def['label'] = $this->make_label($column);
#
#            if (! isset($column_def['writeable']))
#                $column_def['writeable'] = 1;
#
#            if (! isset($column_def['type'])) {
#                if ($data_type == 'date' ||
#                    $data_type == 'datetime' ||
#                    $data_type == 'time') {
#                    $column_def['type'] = $data_type;
#                }
#                elseif ($data_type == 'timestamp') {
#                    $column_def['type'] = 'datetime';
#                }
#                elseif (preg_match('/int/',$data_type) ||
#                        preg_match('/signed/',$data_type) ||
#                        ($data_type == 'number' && $data_scale == 0)) {
#                    $column_def['type'] = 'integer';
#                }
#                elseif ($data_type == 'number' ||
#                        $data_type == 'float' ||
#                        $data_type == 'real' ||
#                        $data_type == 'decimal' ||
#                        $data_type == 'double') {
#                    $column_def['type'] = 'float';
#                }
#                elseif (! isset($char_length) ||
#                        $char_length < 512) {
#                    $column_def['type'] = 'string';
#                }
#                else {
#                    $column_def['type'] = 'text';
#                }
#            }
#
#            if (! isset($column_def['maxlength'])) {
#                if ($char_length) {
#                    $column_def['maxlength'] = $char_length;
#                }
#                elseif ($data_precision) {
#                    $column_def['maxlength'] = $data_precision + 2;  # approx. accomodates "-" (negative) prefix and decimal point
#                }
#            }
#
#            if (! in_array($column, $columns))
#                array_push($columns, $column);
#        }
#        if ($trace) trace_exit();
#    }
#
#    protected function _get_indexes ($table, $schema_name, &$table_def) {
#        global $trace;
##        if ($trace) trace_entry();
#        $table_def['primary_key']    = Array();
#        $table_def['unique_indexes'] = Array();
#        $table_def['indexes']        = Array();
#        if ($trace) trace_exit();
#    }

    protected function _get_select_sth ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $sql  = $this->_get_select_sql($table, $params, $columns, $options);
        if ($debug_sql) fwrite($debug_fh, "<!-- DEBUG_SQL:\n" . $sql . " -->\n");
        $sth = oci_parse($this->dbh, $sql);
        if (isset($sth)) {
            oci_execute($sth);
            if (isset($options) && isset($options['query_def'])) {
                if (! is_array($options['query_def'])) $options['query_def'] =  array();
                $query_def            =& $options['query_def'];
                $query_def['column']  =  array();
                $column_defs          =& $query_def['column'];
                $query_def['columns'] =  array();
                $columns              =& $query_def['columns'];
                $num_fields = oci_num_fields($sth);
                for ($i = 1; $i <= $num_fields; $i++) {
                    $column_name = strtolower(oci_field_name($sth, $i));
                    $column_defs[$column_name] = array('column_name'  => $column_name,
                                                       'column_label' => $this->make_label($column_name),
                                                       'column_type'  => $this->native_type_to_app_type(oci_field_type($sth,$i)));
                    $columns[] = $column_name;
                }
            }
        }
        else {
            $message = 'Error in SQL: [' . oci_error() . ']';
            throw new \Exception($message . ' : ' . $sql);
        }
        if ($trace) trace_exit($sth);
        return($sth);
    }

    public function get_rows ($table, $params = null, $columns = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        #echo "get_rows($table, $params, columns=[", implode(",",$columns), "], $options)\n";
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);

        $offset = 0;
        $limit = -1;
        if (isset($options)) {
            if (isset($options['limit'])) {
                $limit = $options['limit'];
            }
            if (isset($options['offset']) && $options['offset']) {
                $offset = $options['offset'];
            }
        }

        $rows = array();
        $nrows = oci_fetch_all($sth, $rows, $offset, $limit, OCI_FETCHSTATEMENT_BY_ROW+OCI_NUM);
        if ($trace) trace_exit($nrows.' rows');
        return($rows);
    }

    public function get_hashes ($table, $params = null, $columns = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $rows = Array();
        if (isset($columns)) {
            $rows_array = $this->get_rows($table, $params, $columns, $options);
            $nrows = count($rows_array);
            if ($nrows > 0) {
                $ncols = count($rows_array[0]);
                foreach ($rows_array as $row_array) {
                    $row = Array();
                    for ($i = 0; $i < $ncols; $i++) {
                        $row[$columns[$i]] = $row_array[$i];
                    }
                    $rows[] = $row;
                }
            }
        }
        else {
            $sth  = $this->_get_select_sth($table, $params, $columns, $options);

            $offset = 0;
            $limit = -1;
            if (isset($options)) {
                if (isset($options['limit'])) {
                    $limit = $options['limit'];
                }
                if (isset($options['offset']) && $options['offset']) {
                    $offset = $options['offset'];
                }
            }

            $rows      = array();
            $rows_hash = Array();
            $nrows = oci_fetch_all($sth, $rows_hash, $offset, $limit, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);
            if ($nrows > 0) {
                $ncols = count($rows_hash[0]);
                foreach ($rows_hash as $row_hash) {
                    $row = Array();
                    foreach ($row_hash as $uc_column => $value) {
                        $row[strtolower($uc_column)] = $value;
                    }
                    $rows[] = $row;
                }
            }
        }
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_column ($table, $params = null, $column = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $column_values = Array();
        $rows_array = $this->get_rows($table, $params, Array($column), $options);
        $nrows = count($rows_array);
        if ($nrows > 0) {
            foreach ($rows_array as $row_array) {
                $column_values[] = $row_array[0];
            }
        }
        if ($trace) trace_exit($column_values);
        return($column_values);
    }

    protected function _make_limit_clause ($table, $options) {
        $limit_clause = '';
        return($limit_clause);
    }

    public function remove_limit_clause (&$sql) {
        # do nothing
    }

    public function _quote ($str_value) {
        preg_replace('/\'/', '\'\'', $str_value);
        return("'" . $str_value . "'");
    }

    public function date_stdformat ($dbexpr) {
        return("to_char($dbexpr,'YYYY-MM-DD')");
    }

    public function datetime_to_hour ($dbexpr) {
        return("to_char($dbexpr,'YYYY-MM-DD HH24:00:00')");
    }

    public function datetime_stdformat ($dbexpr) {
        return("to_char($dbexpr,'YYYY-MM-DD HH24:MI:SS')");
    }

    public function date_literal ($year, $month, $day) {
        return("to_date('$year-$month-$day','YYYY-MM-DD')");
    }

    public function datetime_literal ($year, $month, $day, $hours = '00', $min = '00', $sec = '00') {
        return("to_date('$year-$month-$day $hours:$min:$sec','YYYY-MM-DD HH24:MI:SS')");
    }

    public function relative_date_literal ($days, $base_date = null) {
        if ($base_date == null) return("(trunc(sysdate) + INTERVAL $days DAY)");
        else                    return("(to_date('$base_date','YYYY-MM-DD') + INTERVAL $days DAY)");
    }

    public function relative_datetime_literal ($days, $hours = '00', $min = '00', $sec = '00', $base_datetime = null) {
        if ($days == intval($days)) {
            if ($base_datetime == null) return("(sysdate + INTERVAL '$days $hours:$min:$sec' DAY TO SECOND)");
            else                        return("(to_date('$base_datetime','YYYY-MM-DD HH24:MI:SS') + INTERVAL '$days $hours:$min:$sec' DAY TO SECOND)");
        }
        else {
            if ($base_datetime == null) return("(sysdate + INTERVAL $days DAY)");
            else                        return("(to_date('$base_datetime','YYYY-MM-DD HH24:MI:SS') + INTERVAL $days DAY)");
        }
    }
}

